<?php
	
	/* Ce fichier permet de créer toutes les tables dans la BDD,
	 * les informations de connexion DOIVENT être définies dans
	 * le fichier inc/defines.php au préalable.
	 * 
	 * Un compte admin/admin est aussi crée par défaut.
	 */
	
	error_reporting(E_ALL);
	ini_set('display_errors', true);
	
	include_once "inc/defines.php";
	
	//Création des tables
	$sql["admin"] = "CREATE TABLE ".ADMIN_TABLE."(id_admin INT AUTO_INCREMENT, login VARCHAR(32) UNIQUE NOT NULL, password VARCHAR(32) NOT NULL, PRIMARY KEY(id_admin));";
	$sql["dde"] = "CREATE TABLE ".DDE_TABLE."(id_dde INT AUTO_INCREMENT, login VARCHAR(32) UNIQUE NOT NULL, password VARCHAR(32) NOT NULL, PRIMARY KEY(id_dde));";
	$sql["formation"] = "CREATE TABLE ".FORM_TABLE."(id_form INT AUTO_INCREMENT, nom_form VARCHAR(32) NOT NULL, PRIMARY KEY(id_form));";
	$sql["suit"] = "CREATE TABLE ".SUIT_TABLE."(id_dde INT NOT NULL, id_form INT NOT NULL, PRIMARY KEY(id_dde, id_form), FOREIGN KEY(id_dde) REFERENCES ".DDE_TABLE."(id_dde) ON DELETE CASCADE, FOREIGN KEY(id_form) REFERENCES ".FORM_TABLE."(id_form) ON DELETE CASCADE);";
	$sql["semestre"] = "CREATE TABLE ".SEMESTRE_TABLE."(id_sem INT AUTO_INCREMENT, id_form INT NOT NULL, nom_sem VARCHAR(32) NOT NULL, PRIMARY KEY(id_sem), FOREIGN KEY(id_form) REFERENCES ".FORM_TABLE."(id_form) ON DELETE CASCADE);";
	$sql["parcours"] = "CREATE TABLE ".PARCOURS_TABLE."(id_parcours INT AUTO_INCREMENT, id_sem INT NOT NULL, nom_parcours VARCHAR(32) NOT NULL, PRIMARY KEY(id_parcours), FOREIGN KEY(id_sem) REFERENCES ".SEMESTRE_TABLE."(id_sem) ON DELETE CASCADE);";
	$sql["ue"] = "CREATE TABLE ".UE_TABLE."(id_ue INT AUTO_INCREMENT, id_sem INT NOT NULL, nom_ue VARCHAR(32) NOT NULL, PRIMARY KEY(id_ue), FOREIGN KEY(id_sem) REFERENCES ".SEMESTRE_TABLE."(id_sem) ON DELETE CASCADE);";
	$sql["module"] = "CREATE TABLE ".MODULE_TABLE."(id_module INT AUTO_INCREMENT, id_ue INT, id_parcours INT, nom_module VARCHAR(32) NOT NULL, PRIMARY KEY(id_module), FOREIGN KEY(id_ue) REFERENCES ".UE_TABLE."(id_ue) ON DELETE CASCADE, FOREIGN KEY(id_parcours) REFERENCES ".PARCOURS_TABLE."(id_parcours) ON DELETE CASCADE);";
	$sql["examen"] = "CREATE TABLE ".EXAM_TABLE."(id_examen INT AUTO_INCREMENT, id_module INT NOT NULL, nom_examen VARCHAR(32) NOT NULL, coefficient FLOAT NOT NULL, PRIMARY KEY(id_examen), FOREIGN KEY(id_module) REFERENCES ".MODULE_TABLE."(id_module) ON DELETE CASCADE);";
	$sql["td"] = "CREATE TABLE ".GRP_TD_TABLE."(id_grptd INT AUTO_INCREMENT, id_sem INT NOT NULL, nom_grptd VARCHAR(32) NOT NULL, PRIMARY KEY(id_grptd), FOREIGN KEY(id_sem) REFERENCES ".SEMESTRE_TABLE."(id_sem) ON DELETE CASCADE);";
	$sql["tp"] = "CREATE TABLE ".GRP_TP_TABLE."(id_grptp INT AUTO_INCREMENT, id_grptd INT NOT NULL, nom_grptp VARCHAR(32) NOT NULL, PRIMARY KEY(id_grptp), FOREIGN KEY(id_grptd) REFERENCES ".GRP_TD_TABLE."(id_grptd) ON DELETE CASCADE);";
	$sql["etudiant"] = "CREATE TABLE ".ETUDIANT_TABLE."(id_etud INT AUTO_INCREMENT, id_grptp INT NOT NULL, id_parcours INT, login VARCHAR(32) UNIQUE NOT NULL, password VARCHAR(32) NOT NULL, nom VARCHAR(32), prenom VARCHAR(32), apogee VARCHAR(32), PRIMARY KEY(id_etud), FOREIGN KEY(id_grptp) REFERENCES ".GRP_TP_TABLE."(id_grptp) ON DELETE CASCADE, FOREIGN KEY(id_parcours) REFERENCES ".PARCOURS_TABLE."(id_parcours) ON DELETE SET NULL);"; //on delete set null pour que quand on supprime un parcours de semestre cela desinscrive les etudiants qui y etait inscrits
	$sql["capitalisee"] = "CREATE TABLE ".UE_CAP_TABLE."(id_uecap INT AUTO_INCREMENT, id_etud INT NOT NULL, id_ue INT NOT NULL, moyenne_uecap FLOAT NOT NULL, PRIMARY KEY(id_uecap), FOREIGN KEY(id_etud) REFERENCES ".ETUDIANT_TABLE."(id_etud) ON DELETE CASCADE, FOREIGN KEY(id_ue) REFERENCES ".UE_TABLE."(id_ue) ON DELETE CASCADE);";
	$sql["note"] = "CREATE TABLE ".NOTE_TABLE."(id_note INT AUTO_INCREMENT, id_etud INT NOT NULL, id_examen INT NOT NULL, note FLOAT NOT NULL, simulee BOOLEAN NOT NULL, PRIMARY KEY(id_note), FOREIGN KEY(id_etud) REFERENCES ".ETUDIANT_TABLE."(id_etud) ON DELETE CASCADE, FOREIGN KEY(id_examen) REFERENCES ".EXAM_TABLE."(id_examen) ON DELETE CASCADE);";
	
	//Création du compte admin
	$sql["compteAdmin"] = "INSERT INTO ".ADMIN_TABLE." VALUES(NULL, \"admin\", \"admin\");";
	
	echo "<b>Cr&eacute;ation des tables dans la BDD...</b><br>";
	$db = new PDO(SERVER.":host=".HOST.";dbname=".BASE, USER, PASS);
	$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$i = 0;
	foreach($sql as $query) {
		echo $query."<br>";
		$db->exec($query);
		$i++;
	}
	$db = null;
	echo "<b>$i / ".count($sql)." requ&ecirc;tes ex&eacute;cut&eacute;es.</b>";
	
?>
